package com.ruoyi.common.excel.utils;


import com.ruoyi.common.core.exception.base.BaseException;
import com.ruoyi.common.excel.bean.PoiBean;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Date;

/**
 * 表格样式工具类
 */
public class PoiUtils {
    private static final Logger log = LoggerFactory.getLogger(PoiUtils.class);


    /**
     * 设置表格样式(加边框)
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle setCellStyle(XSSFWorkbook workbook, boolean haveBorder, boolean haveCenter, Font font, IndexedColors color) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        if (haveBorder) {
            // 设置边框样式
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);

            // 设置边框颜色
            cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        }
        if (haveCenter) {
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }
        if (color != null) {
            cellStyle.setFillForegroundColor(color.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        cellStyle.setFont(font);
        return cellStyle;
    }


    /**
     * 设置表格样式(加边框)
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 设置边框样式
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        // 设置边框颜色
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        return cellStyle;
    }


    /**
     * 设置表格样式(无边框)
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createCellStyle2(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        return cellStyle;
    }

    /**
     * 设置表格样式(颜色)
     *
     * @param workbook
     * @return
     */
    public static XSSFCellStyle createCellStyleColor(XSSFWorkbook workbook, IndexedColors color) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        // 设置边框样式
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        cellStyle.setFillForegroundColor(color.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return cellStyle;
    }


    /**
     * 计算excel指定范围相加之和
     *
     * @param sheet
     * @param columnIndex
     * @param startRow
     * @param endRow
     * @return
     */
    public static BigDecimal sumColumnDataInRange(Sheet sheet, int columnIndex, int startRow, int endRow) {
        BigDecimal sum = BigDecimal.valueOf(0.0);
        try {
            for (int i = startRow; i <= endRow; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        String stringCellValue = cell.getStringCellValue();
                        //去掉百分号
                        if (stringCellValue.contains("%")) {
                            stringCellValue = stringCellValue.substring(0, stringCellValue.length() - 1);
                        }
                        BigDecimal bigDecimal = new BigDecimal(stringCellValue);
                        sum = sum.add(bigDecimal);
                    }
                }
            }
        } catch (Exception e) {
            throw new BaseException("数据错误,求和失败");
        }
        return sum.setScale(2, RoundingMode.HALF_UP);
    }

    public static PoiBean sumColumnDataInRange1(Sheet sheet, int columnIndex, int startRow, int endRow) {
        PoiBean poiBean = new PoiBean();
        poiBean.setHavePercent(false);
        BigDecimal sum = BigDecimal.valueOf(0.0);
        try {
            for (int i = startRow; i <= endRow; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        String stringCellValue = cell.getStringCellValue();
                        //去掉百分号
                        if (stringCellValue.contains("%")) {
                            stringCellValue = stringCellValue.substring(0, stringCellValue.length() - 1);
                            poiBean.setHavePercent(true);
                        }
                        BigDecimal bigDecimal = new BigDecimal(stringCellValue);
                        sum = sum.add(bigDecimal);
                    }
                }
            }
        } catch (Exception e) {
            throw new BaseException("数据错误,求和失败");
        }
        poiBean.setSum(sum.setScale(2, RoundingMode.HALF_UP));
        return poiBean;
    }

    public static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    // 获取单元格的值并转为整数类型
    public static int getCellValueAsInt(Cell cell) {
        if (cell == null) {
            return 0;
        }

        cell.setCellType(CellType.NUMERIC);
        return (int) cell.getNumericCellValue();
    }

    public static BigDecimal getCellValueAsBigDecimal(Cell cell) {
        if (cell == null) {
            return BigDecimal.ZERO;
        }
        cell.setCellType(CellType.NUMERIC);
        return BigDecimal.valueOf(cell.getNumericCellValue()).setScale(2, RoundingMode.HALF_UP);
    }

    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));

        return tDate;
    }

}
